﻿GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupSchemeGet]
	@GroupId				INT
AS
BEGIN

	DECLARE
		@TableName		NVARCHAR(100),
		@IsExTable			BIT,
		@Parent					INT,
		@Sql						NVARCHAR(4000)

	SELECT
		@TableName = name,
		@IsExTable = CASE typ WHEN 'TE' THEN 1 ELSE 0 END,
		@Parent = parent
	FROM
		n_Tree
	WHERE
		id = @GroupId

	SET
		@Sql = 'SELECT TOP 1 * FROM ' + @TableName + ' t'

	IF @IsExTable = 1
	BEGIN
		SELECT @TableName = name FROM n_Tree WHERE id = @Parent
		SET @Sql = @Sql + ' INNER JOIN ' + @TableName + ' pt ON t.idx = pt.idx'
	END
	
	EXEC sp_executesql @Sql

END
GO
ALTER PROCEDURE [dbo].[bhp_NemoGroupColumnsGet]
	@GroupId				INT
AS
BEGIN
	SELECT 
		id as ColumnId,
		name as ColumnName,
		ct.EnumType as ColumnType,
		ISNULL(t.rel, 0) as RelatedGroupId,
		ISNULL(c.DisplayName, name) as DisplayName,
		ISNULL(c.FormattingValue, '{0}') as FormattingValue,
		ISNULL(c.Sort, 0) as ColumnSort,
		c.RegexValidation,
		c.[Description],
		ISNULL(c.HideForEdition, 0) as HideForEdition,
		c.CustomEditControl,
		t.parent as BelongsToGroupId
	FROM
		n_Tree t
		INNER JOIN bht_ColumnTypes ct ON t.typ = ct.ColumnType
		LEFT JOIN bht_Columns c ON t.id = c.ColumnId
	WHERE
		(parent = @GroupId OR parent = (SELECT TOP 1 parent FROM n_Tree WHERE id = @GroupId))
		AND (t.del IS NULL OR t.del = 0)
END